package com.alibaba.seckill.controller;

import com.alibaba.seckill.utils.ExcelTool;
import lombok.SneakyThrows;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author 47320
 */
@Controller
@RequestMapping("/test")
public class TestController {
    public static int totalRows; //sheet中总行数
    public static int totalCells; //每一行总单元格数
    private final static String XLS = "xls";
    private final static String XLSX = "xlsx";

    @RequestMapping("test")
    public String importExcel(@RequestParam("myfile") MultipartFile myFile) {
        ModelAndView modelAndView = new ModelAndView();
        try {
            Integer num = this.importiExcel(myFile);
        } catch (Exception e) {
            modelAndView.addObject("msg", e.getMessage());
            return "index";
        }
        modelAndView.addObject("msg", "数据导入成功");
        return "index";
    }

    @SneakyThrows
    private Integer importiExcel(MultipartFile myFile) {
        Workbook workbook = null;
        String fileName = myFile.getOriginalFilename();
        if (fileName.endsWith(XLS)) {
            //2003
            workbook = new HSSFWorkbook(myFile.getInputStream());
        } else if (fileName.endsWith(XLSX)) {
            //2007
            workbook = new XSSFWorkbook(myFile.getInputStream());
        } else {
            throw new Exception("文件不是Excel文件");
        }
        Sheet sheet = workbook.getSheet("Sheet1");
        int rows = sheet.getLastRowNum();// 指的行数，一共有多少行+
        if (rows == 0) {
            throw new Exception("请填写数据");
        }
        for (int i = 1; i <= rows + 1; i++) {
            // 读取左上端单元格
            Row row = sheet.getRow(i);
            // 行不为空
            if (row != null) {
                // **读取cell**
                Student student = new Student();
                //姓名
                String name = getCellValue(row.getCell(0));
                student.setName(name);
                //班级
                String classes = getCellValue(row.getCell(1));
                student.setClasses(classes);
                //分数
                String scoreString = getCellValue(row.getCell(2));
                if (!StringUtils.isEmpty(scoreString)) {
                    Integer score = Integer.parseInt(scoreString);
                    student.setScore(score);
                }
                //考试时间
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//小写的mm表示的是分钟
                String dateString = getCellValue(row.getCell(3));
                if (!StringUtils.isEmpty(dateString)) {
                    Date date = sdf.parse(dateString);
                    student.setTime(date);
                }
                System.out.println("dateString = " + dateString);
            }
        }
        return rows - 1;
    }

    /**
     * 获得Cell内容
     *
     * @param cell
     * @return
     */
    public String getCellValue(Cell cell) {
        String value = "";
        if (cell != null) {
            // 以下是判断数据的类型
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                    value = cell.getNumericCellValue() + "";
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        } else {
                            value = "";
                        }
                    } else {
                        value = new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING: // 字符串
                    value = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                    value = cell.getBooleanCellValue() + "";
                    break;
                case HSSFCell.CELL_TYPE_FORMULA: // 公式
                    value = cell.getCellFormula() + "";
                    break;
                case HSSFCell.CELL_TYPE_BLANK: // 空值
                    value = "";
                    break;
                case HSSFCell.CELL_TYPE_ERROR: // 故障
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;
            }
        }
        return value.trim();
    };
}
